const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'pet_rescue',
  charset: 'utf8mb4',
  connectionLimit: 10,
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true
};

// 创建连接池
const pool = mysql.createPool(dbConfig);

// 测试数据库连接
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    console.log('🗄️  数据库连接成功！');
    console.log(`📊 数据库信息: ${dbConfig.user}@${dbConfig.host}:${dbConfig.port}/${dbConfig.database}`);
    connection.release();
    return true;
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message);
    console.error('💡 请检查数据库配置或确保MySQL服务正在运行');
    return false;
  }
}

// 执行查询的通用方法
async function query(sql, params = []) {
  try {
    const [results] = await pool.execute(sql, params);
    return results;
  } catch (error) {
    console.error('SQL执行错误:', error.message);
    console.error('SQL语句:', sql);
    console.error('参数:', params);
    throw error;
  }
}

// 执行事务
async function transaction(callback) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

// 分页查询助手
async function paginatedQuery(sql, params = [], page = 1, pageSize = 10) {
  const offset = (page - 1) * pageSize;
  
  try {
    // 简化的计数查询 - 创建子查询
    const countSql = `SELECT COUNT(*) as total FROM (${sql}) as count_table`;
    console.log('分页查询 - 原始SQL:', sql);
    console.log('分页查询 - 计数SQL:', countSql);
    console.log('分页查询 - 参数:', params);
    
    const [countResult] = await query(countSql, params);
    console.log('分页查询 - 计数结果:', countResult);
    const total = countResult.total || 0;
    
    // 获取分页数据
    const dataSql = sql + ` LIMIT ${pageSize} OFFSET ${offset}`;
    const data = await query(dataSql, params);
    
    return {
      data,
      total,
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      totalPages: Math.ceil(total / pageSize)
    };
  } catch (error) {
    console.error('分页查询失败:', error);
    // 如果计数查询失败，退回到简单模式
    const data = await query(sql + ` LIMIT ${pageSize} OFFSET ${offset}`, params);
    return {
      data,
      total: data.length, // 至少返回当前页的数量
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      totalPages: 1 // 无法确定总页数时返回1
    };
  }
}

module.exports = {
  pool,
  query,
  transaction,
  paginatedQuery,
  testConnection
};
